﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;


namespace CCPSIP
{
    public class OracleKitVCardGE
    {
        private const string kOracleServer = "ISENDER.CCP.COM.TW";
        private const string kOracleUserId = "ISENDER";
        private static byte[] kOracleKey = { 0x61, 0x4B, 0x68, 0x4A, 0x4A, 0x46, 0x41, 0x32, 0x73, 0x57, 0x66, 0x45, 0x73, 0x51, 0x7A, 0x30, 0x5A, 0x6D, 0x35, 0x69, 0x77, 0x51, 0x69, 0x39, 0x67, 0x6A, 0x69, 0x56, 0x4E, 0x44 };
        private const int kAllowMinutes = 30;
        private const int kFlexibleMinutes = 5;
        private const int kAllowDistance = 10 * 1000;// in meters

        private static string GetConnStrMIS()
        {
            return String.Format(
              "Data Source={0};Persist Security Info=True;User ID={1};Password={2}",
              kOracleServer, kOracleUserId, System.Text.Encoding.UTF8.GetString(kOracleKey));
        }
        /// <summary>
        /// 儲存新的群組名片交換資料
        /// </summary>
        /// <param name="InitTime">發起時間</param>
        /// <param name="Initiator">發起人帳號</param>
        /// <param name="GEName">群組名片交換名稱(顯示用)</param>
        /// <param name="location">發起人的定位資訊</param>
        /// <returns>群組名片交換的ClientKey</returns>
        public static string NewVCardGE(DateTime InitTime, string Initiator, string GEName, LocationInfo location)
        {
            string ClientKey = "";
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();
                myTrans = connOracle.BeginTransaction();


                //【01】取消舊的未完成交換(相同Host)
                //
                string updStr1 = "UPDATE vcard_ge_main"
                  + " SET Status='C', UpdateTime=:UpdateTime"
                  + " WHERE Status='W' AND Host=:Host";
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle, myTrans);
                updCmd1.Parameters.Add("UpdateTime", OracleType.DateTime).Value = InitTime;
                updCmd1.Parameters.Add("Host", OracleType.VarChar).Value = Initiator;
                updCmd1.ExecuteNonQuery();


                //【02】儲存群組名片交換 main table
                //
                string insStr2 = "INSERT INTO vcard_ge_main"
                  + " (InitTime, Initiator, Status, UpdateTime, Host, GEName, Pushed) VALUES"
                  + " (:InitTime, :Initiator, 'W', :InitTime, :Initiator, :GEName , :Pushed)"
                  + " RETURNING pid INTO :pid";
                OracleCommand insCmd2 = new OracleCommand(insStr2, connOracle, myTrans);
                insCmd2.Parameters.Add("InitTime", OracleType.DateTime).Value = InitTime;
                insCmd2.Parameters.Add("Initiator", OracleType.VarChar).Value = Initiator;
                insCmd2.Parameters.Add("GEName", OracleType.VarChar).Value = GEName;
                insCmd2.Parameters.Add("Pushed", OracleType.VarChar).Value = "N";
                insCmd2.Parameters.Add(new OracleParameter
                {
                    ParameterName = ":pid",
                    OracleType = OracleType.Number,
                    Direction = ParameterDirection.Output
                });

                insCmd2.ExecuteNonQuery();
                int VGEID = Convert.ToInt32(insCmd2.Parameters[":pid"].Value);

                //【03】產生ClientKey並儲存
                //
                string randomStr = ChrisKit.GetRandomStr(8, true).ToUpper();
                ClientKey = string.Format("{0}_{1}", VGEID, randomStr);

                string updStr3 = "UPDATE vcard_ge_main SET ClientKey=:ClientKey"
                  + " WHERE pid=:pid";
                OracleCommand updCmd3 = new OracleCommand(updStr3, connOracle, myTrans);
                updCmd3.Parameters.Add("ClientKey", OracleType.VarChar).Value = ClientKey;
                updCmd3.Parameters.Add("pid", OracleType.Int32).Value = VGEID;
                updCmd3.ExecuteNonQuery();


                //【04】儲存群組名片交換 member table
                //
                string insStr4 = "INSERT INTO vcard_ge_member"
                  + " (VGEID, MemberID, JoinTime, UpdateTime, Status, Latitude, Longitude, HAccuracy) VALUES"
                  + " (:VGEID, :MemberID, :InitTime, :InitTime, :Status, :Latitude, :Longitude, :HAccuracy)";
                OracleCommand insCmd4 = new OracleCommand(insStr4, connOracle, myTrans);
                insCmd4.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                insCmd4.Parameters.Add("MemberID", OracleType.VarChar).Value = Initiator;
                insCmd4.Parameters.Add("InitTime", OracleType.DateTime).Value = InitTime;
                insCmd4.Parameters.Add("Status", OracleType.VarChar).Value = "Y";
                insCmd4.Parameters.Add("Latitude", OracleType.Number).Value = location.Latitude;
                insCmd4.Parameters.Add("Longitude", OracleType.Number).Value = location.Longitude;
                insCmd4.Parameters.Add("HAccuracy", OracleType.Int32).Value = location.HAccuracy;
                insCmd4.ExecuteNonQuery();


                //【99】Commit Transaction
                //
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }

            return ClientKey;
        }


        /// <summary>
        /// 取得附近的群組名片交換資訊
        /// </summary>
        /// <param name="loc">偵測人的定位資訊</param>
        /// <returns>群組名片交換Dictionary Array</returns>
        public static Dictionary<string, string>[] getNearbyVCardGE(LocationInfo loc)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                //【01】查詢群組名片交換資訊
                //
                string selStr1 = "SELECT m1.pid, m1.ClientKey, m1.InitTime, m1.GEName, m1.Host, u.CustomName,"
                  + "   m2.Latitude, m2.Longitude, m2.HAccuracy"
                  + " FROM vcard_ge_main m1, vcard_ge_member m2, reg_user u"
                  + " WHERE m1.Status='W' AND m1.InitTime>=:InitTime"
                  + "   AND m1.pid=m2.VGEID AND m1.Host=m2.MemberID"
                  + "   AND m1.Host=u.SIP_Num AND u.Status='Y'"
                  + " ORDER BY InitTime, pid";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                DateTime allowTime = loc.UpdateTime.AddMinutes(-1 * kAllowMinutes);
                selCmd1.Parameters.Add("InitTime", OracleType.DateTime).Value = allowTime;
                OracleDataAdapter adapter1 = new OracleDataAdapter(selCmd1);
                DataSet dataSet1 = new DataSet();
                adapter1.Fill(dataSet1);

                List<Dictionary<string, string>> dictList = new List<Dictionary<string, string>>();

                foreach (DataRow row in dataSet1.Tables[0].Rows)
                {
                    //【02】計算與Host的距離，太遠則排除
                    //
                    decimal hostLat = Convert.ToDecimal(row["Latitude"]);
                    decimal hostLng = Convert.ToDecimal(row["Longitude"]);
                    int hostAccuracy = Convert.ToInt32(row["HAccuracy"]);

                    double distance = ChrisKit.Distance(hostLat, hostLng, loc.Latitude, loc.Longitude);
                    if (hostAccuracy > 0)
                        distance = distance - hostAccuracy;
                    if (loc.HAccuracy > 0)
                        distance = distance - loc.HAccuracy;
                    if (distance > kAllowDistance)
                        continue;


                    //【03】查詢目前成員總數
                    //
                    int VGEID = Convert.ToInt32(row["pid"]);
                    string selStr3 = "SELECT COUNT(*) FROM vcard_ge_member"
                      + " WHERE VGEID=:VGEID";
                    OracleCommand selCmd3 = new OracleCommand(selStr3, connOracle);
                    selCmd3.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                    int MemberCount = Convert.ToInt32(selCmd3.ExecuteScalar());


                    //【04】產生 Dictionary 物件
                    //
                    Dictionary<string, string> dict = new Dictionary<string, string>();
                    dict["VGEKey"] = Convert.ToString(row["ClientKey"]);
                    dict["InitTime"] = string.Format("{0:M/d HH:mm}", row["InitTime"]);
                    dict["GEName"] = Convert.ToString(row["GEName"]);
                    dict["Host"] = Convert.ToString(row["Host"]);
                    dict["HostName"] = Convert.ToString(row["CustomName"]);
                    dict["MemberCount"] = MemberCount.ToString();
                    dictList.Add(dict);
                }

                return dictList.ToArray();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 查詢是否有Key對應的等待中群組名片交換
        /// </summary>
        /// <param name="VGEKey">群組名片交換Key</param>
        /// <returns>有此群組名片交換true，否則false</returns>
        public static bool hasWaitingVGE(string VGEKey)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string selStr1 = "SELECT COUNT(*) FROM vcard_ge_main"
                  + " WHERE Status='W' AND InitTime>=:InitTime AND ClientKey=:ClientKey";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                DateTime allowTime = DateTime.Now.AddMinutes(-1 * kAllowMinutes - kFlexibleMinutes);
                selCmd1.Parameters.Add("InitTime", OracleType.DateTime).Value = allowTime;
                selCmd1.Parameters.Add("ClientKey", OracleType.VarChar).Value = VGEKey;
                int count = Convert.ToInt32(selCmd1.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得群組名片交換的PID
        /// </summary>
        /// <param name="VGEKey">群組名片交換Key</param>
        /// <param name="connOracle">現有的OracleConnection</param>
        /// <returns>若有此VGEKey回傳PID，否則-1</returns>
        private static int getVGEID(string VGEKey, OracleConnection connOracle)
        {
            string selStr1 = "SELECT pid FROM vcard_ge_main WHERE ClientKey=:ClientKey";
            OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
            selCmd1.Parameters.Add("ClientKey", OracleType.VarChar).Value = VGEKey;
            object result = selCmd1.ExecuteScalar();
            if (result != DBNull.Value)
                return Convert.ToInt32(result);
            else
                return -1;
        }


        /// <summary>
        /// 加入群組名片交換
        /// </summary>
        /// <param name="VGEKey">群組名片交換Key</param>
        /// <param name="SIP_Num">User的SIP帳號</param>
        /// <param name="location">User的定位資訊</param>
        public static void JoinVCardGE(string VGEKey, string SIP_Num, LocationInfo location)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                int VGEID = getVGEID(VGEKey, connOracle);

                //【01】檢查是否已經是成員
                //
                string selStr1 = "SELECT COUNT(*) FROM vcard_ge_member"
                  + " WHERE VGEID=:VGEID AND MemberID=:MemberID";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                selCmd1.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                selCmd1.Parameters.Add("MemberID", OracleType.VarChar).Value = SIP_Num;
                int count = Convert.ToInt32(selCmd1.ExecuteScalar());

                if (count == 0)
                {
                    //【02】取消舊的未完成交換(相同Host)
                    //
                    string updStr2 = "UPDATE vcard_ge_main"
                      + " SET Status='C', UpdateTime=:UpdateTime"
                      + " WHERE Status='W' AND Host=:Host";
                    OracleCommand updCmd2 = new OracleCommand(updStr2, connOracle);
                    updCmd2.Parameters.Add("UpdateTime", OracleType.DateTime).Value = location.UpdateTime;
                    updCmd2.Parameters.Add("Host", OracleType.VarChar).Value = SIP_Num;
                    updCmd2.ExecuteNonQuery();

                    //【03】新增到 member table
                    //
                    string insStr3 = "INSERT INTO vcard_ge_member"
                      + " (VGEID, MemberID, JoinTime, UpdateTime, Status, Latitude, Longitude, HAccuracy) VALUES"
                      + " (:VGEID, :MemberID, :JoinTime, :JoinTime, :Status, :Latitude, :Longitude, :HAccuracy)";
                    OracleCommand insCmd3 = new OracleCommand(insStr3, connOracle);
                    insCmd3.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                    insCmd3.Parameters.Add("MemberID", OracleType.VarChar).Value = SIP_Num;
                    insCmd3.Parameters.Add("JoinTime", OracleType.DateTime).Value = location.UpdateTime;
                    insCmd3.Parameters.Add("Status", OracleType.VarChar).Value = "Y";
                    insCmd3.Parameters.Add("Latitude", OracleType.Number).Value = location.Latitude;
                    insCmd3.Parameters.Add("Longitude", OracleType.Number).Value = location.Longitude;
                    insCmd3.Parameters.Add("HAccuracy", OracleType.Int32).Value = location.HAccuracy;
                    insCmd3.ExecuteNonQuery();
                }
                else
                {
                    //【04】已經是成員，更新時間與位置資訊
                    //
                    string updStr4 = "UPDATE vcard_ge_member"
                      + " SET UpdateTime=:UpdateTime, Latitude=:Latitude,"
                      + "     Longitude=:Longitude, HAccuracy=:HAccuracy"
                      + " WHERE VGEID=:VGEID AND MemberID=:MemberID";
                    OracleCommand updCmd4 = new OracleCommand(updStr4, connOracle);
                    updCmd4.Parameters.Add("UpdateTime", OracleType.DateTime).Value = location.UpdateTime;
                    updCmd4.Parameters.Add("Latitude", OracleType.Number).Value = location.Latitude;
                    updCmd4.Parameters.Add("Longitude", OracleType.Number).Value = location.Longitude;
                    updCmd4.Parameters.Add("HAccuracy", OracleType.Int32).Value = location.HAccuracy;
                    updCmd4.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                    updCmd4.Parameters.Add("MemberID", OracleType.VarChar).Value = SIP_Num;
                    updCmd4.ExecuteNonQuery();
                }
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 查詢是否為交換群組成員
        /// </summary>
        /// <param name="VGEKey">群組名片交換Key</param>
        /// <param name="SIP_Num">User的SIP帳號</param>
        /// <returns>是交換群組成員true，否則false</returns>
        public static bool isMember(string VGEKey, string SIP_Num)
        {
            if (string.IsNullOrEmpty(VGEKey))
                return false;

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                int VGEID = getVGEID(VGEKey, connOracle);

                string selStr1 = "SELECT COUNT(*) FROM vcard_ge_member"
                  + " WHERE VGEID=:VGEID AND MemberID=:MemberID";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                selCmd1.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                selCmd1.Parameters.Add("MemberID", OracleType.VarChar).Value = SIP_Num;
                int count = Convert.ToInt32(selCmd1.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 查詢群組名片交換成員
        /// </summary>
        /// <param name="VGEKey">群組名片交換Key</param>
        /// <returns>交換資訊Dictionary</returns>
        public static Dictionary<string, object> getMembers(string VGEKey)
        {
            Dictionary<string, object> resultDict = new Dictionary<string, object>();

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                int VGEID = getVGEID(VGEKey, connOracle);

                //【01】取得群組名片交換資訊
                //
                string selStr1 = "SELECT Host, Status FROM vcard_ge_main WHERE pid=:VGEID";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                selCmd1.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                OracleDataAdapter adapter1 = new OracleDataAdapter(selCmd1);
                DataSet dataSet1 = new DataSet();
                adapter1.Fill(dataSet1);

                if (dataSet1.Tables[0].Rows.Count > 0)
                {
                    DataRow row0 = dataSet1.Tables[0].Rows[0];
                    string Status = Convert.ToString(row0["Status"]);
                    resultDict["VGEKey"] = VGEKey;
                    resultDict["Host"] = Convert.ToString(row0["Host"]);
                    resultDict["Finish"] = (Status == "F" ? "Y" : "N");
                }
                else
                {
                    return resultDict;
                }

                //【02】取得群組名片交換成員
                //
                string selStr2 = "SELECT m2.MemberID, u.CustomName"
                  + " FROM vcard_ge_member m2, reg_user u"
                  + " WHERE m2.VGEID=:VGEID AND m2.MemberID=u.SIP_Num AND u.Status='Y'"
                  + " ORDER BY m2.JoinTime, m2.MemberID";
                OracleCommand selCmd2 = new OracleCommand(selStr2, connOracle);
                selCmd2.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                OracleDataAdapter adapter2 = new OracleDataAdapter(selCmd2);
                DataSet dataSet2 = new DataSet();
                adapter2.Fill(dataSet2);

                List<Dictionary<string, string>> dictList = new List<Dictionary<string, string>>();

                foreach (DataRow row in dataSet2.Tables[0].Rows)
                {
                    Dictionary<string, string> dict = new Dictionary<string, string>();
                    dict["MemberID"] = Convert.ToString(row["MemberID"]);
                    dict["CustomName"] = Convert.ToString(row["CustomName"]);
                    dictList.Add(dict);
                }
                resultDict["members"] = dictList.ToArray();
            }
            finally
            {
                connOracle.Close();
            }
            return resultDict;
        }


        /// <summary>
        /// 查詢是否為交換群組Host
        /// </summary>
        /// <param name="VGEKey">群組名片交換Key</param>
        /// <param name="SIP_Num">User的SIP帳號</param>
        /// <returns>是交換群組Host回傳true，否則false</returns>
        public static bool isHost(string VGEKey, string SIP_Num)
        {
            if (string.IsNullOrEmpty(VGEKey))
                return false;

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string selStr1 = "SELECT COUNT(*) FROM vcard_ge_main"
                  + " WHERE ClientKey=:ClientKey AND Host=:Host";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                selCmd1.Parameters.Add("ClientKey", OracleType.VarChar).Value = VGEKey;
                selCmd1.Parameters.Add("Host", OracleType.VarChar).Value = SIP_Num;
                int count = Convert.ToInt32(selCmd1.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 檢查群組名片交換是否已完成
        /// </summary>
        /// <param name="VGEID">群組名片交換的PID</param>
        /// <param name="connOracle">現有的OracleConnection</param>
        /// <returns>交換已完成true，否則false</returns>
        private static bool isFinished(int VGEID, OracleConnection connOracle)
        {
            string selStr1 = "SELECT COUNT(*) FROM vcard_ge_main"
              + " WHERE pid=:VGEID AND Status='F'";
            OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
            selCmd1.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
            int count = Convert.ToInt32(selCmd1.ExecuteScalar());
            return (count > 0);
        }


        /// <summary>
        /// 退出群組名片交換
        /// </summary>
        /// <param name="VGEKey">群組名片交換Key</param>
        /// <param name="SIP_Num">User的SIP帳號</param>
        public static void LeaveVCardGE(string VGEKey, string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();

                int VGEID = getVGEID(VGEKey, connOracle);

                if (isFinished(VGEID, connOracle))
                {
                    return;
                }

                myTrans = connOracle.BeginTransaction();


                //【01】從 member table 刪除
                //
                string delStr1 = "DELETE FROM vcard_ge_member"
                  + " WHERE VGEID=:VGEID AND MemberID=:MemberID";
                OracleCommand delCmd1 = new OracleCommand(delStr1, connOracle, myTrans);
                delCmd1.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                delCmd1.Parameters.Add("MemberID", OracleType.VarChar).Value = SIP_Num;
                delCmd1.ExecuteNonQuery();

                //【02】檢查退出者是否為Host
                //
                string selStr2 = "SELECT COUNT(*) FROM vcard_ge_main"
                  + " WHERE pid=:VGEID AND Host=:Host";
                OracleCommand selCmd2 = new OracleCommand(selStr2, connOracle, myTrans);
                selCmd2.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                selCmd2.Parameters.Add("Host", OracleType.VarChar).Value = SIP_Num;
                int count = Convert.ToInt32(selCmd2.ExecuteScalar());

                if (count > 0)
                {
                    //【03】找出下一順位成員
                    //
                    string selStr3 = "SELECT MemberID FROM vcard_ge_member"
                      + " WHERE VGEID=:VGEID ORDER BY JoinTime, MemberID";
                    OracleCommand selCmd3 = new OracleCommand(selStr3, connOracle, myTrans);
                    selCmd3.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                    string newHost = Convert.ToString(selCmd3.ExecuteScalar());


                    //【04】設定新Host
                    //
                    string updStr4 = "UPDATE vcard_ge_main SET Host=:Host"
                      + (string.IsNullOrEmpty(newHost) ? " ,Status='C'" : "")
                      + " WHERE pid=:VGEID";
                    OracleCommand updCmd4 = new OracleCommand(updStr4, connOracle, myTrans);
                    updCmd4.Parameters.Add("Host", OracleType.VarChar).Value = newHost;
                    updCmd4.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                    updCmd4.ExecuteNonQuery();
                }

                //【99】Commit Transaction
                //
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 完成群組名片交換
        /// </summary>
        /// <param name="VGEKey">群組名片交換Key</param>
        /// <param name="ConfirmArray">確認交換名單Array</param>
        public static void FinishVCardGE(string VGEKey, string[] ConfirmArray)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();

                int VGEID = getVGEID(VGEKey, connOracle);

                myTrans = connOracle.BeginTransaction();

                List<string> memberList = new List<string>();


                //【01】排除確認名單以外的成員
                //
                string confirmStr = string.Format("('{0}')", string.Join("','", ConfirmArray));
                string updStr1 = "UPDATE vcard_ge_member SET Status='B'"
                  + " WHERE VGEID=:VGEID AND MemberID NOT IN " + confirmStr;
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle, myTrans);
                updCmd1.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                updCmd1.ExecuteNonQuery();


                //【02】取得確認後成員名單
                //
                string selStr2 = "SELECT MemberID FROM vcard_ge_member"
                  + " WHERE VGEID=:VGEID AND Status='Y'"
                  + " ORDER BY MemberID";
                OracleCommand selCmd2 = new OracleCommand(selStr2, connOracle, myTrans);
                selCmd2.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                OracleDataAdapter adapter2 = new OracleDataAdapter(selCmd2);
                DataSet dataSet2 = new DataSet();
                adapter2.Fill(dataSet2);
                foreach (DataRow row in dataSet2.Tables[0].Rows)
                {
                    memberList.Add(Convert.ToString(row["MemberID"]));
                }


                //【03】展開 vCard 交換下載矩陣
                //
                string insStr3 = "INSERT INTO vcard_ge_dl"
                  + " (VGEID, MemberID, vCardOwner) VALUES"
                  + " (:VGEID, :MemberID, :vCardOwner)";

                OracleCommand insCmd3 = new OracleCommand(insStr3, connOracle, myTrans);
                insCmd3.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                insCmd3.Parameters.Add("MemberID", OracleType.VarChar);
                insCmd3.Parameters.Add("vCardOwner", OracleType.VarChar);

                foreach (string MemberID in memberList)
                {
                    foreach (string vCardOwner in memberList)
                    {
                        if (vCardOwner == MemberID)
                            continue;

                        insCmd3.Parameters["MemberID"].Value = MemberID;
                        insCmd3.Parameters["vCardOwner"].Value = vCardOwner;
                        insCmd3.ExecuteNonQuery();
                    }
                }


                //【04】把交換狀態標記為"完成"
                //
                string updStr4 = "UPDATE vcard_ge_main SET Status='F'"
                  + " WHERE pid=:VGEID";
                OracleCommand updCmd4 = new OracleCommand(updStr4, connOracle, myTrans);
                updCmd4.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                updCmd4.ExecuteNonQuery();


                //【98】Commit Transaction
                //
                myTrans.Commit();


                //【99】設定朋友關係
                //
                SipUserInfo[] mbInfos = new SipUserInfo[memberList.Count];
                for (int i = 0; i < mbInfos.Length; i++)
                {
                    mbInfos[i] = SipKit.GetUserInfo(memberList[i], connOracle);
                }

                for (int i = 0; i < mbInfos.Length; i++)
                {
                    for (int j = i + 1; j < mbInfos.Length; j++)
                    {
                        OracleKitContact.MakeFriends(mbInfos[i], mbInfos[j], "vCardGE", connOracle);
                    }
                }

            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 查詢可下載的群組交換vCard
        /// </summary>
        /// <param name="SIP_Num">User的SIP帳號</param>
        /// <returns>群組交換vCard Dictionary Array</returns>
        public static Dictionary<string, string>[] getToDownloadList(string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string selStr1 = "SELECT d.VGEID, d.vCardOwner, m2.UpdateTime, m1.InitTime"
                  + " FROM vcard_ge_dl d, vcard_ge_member m2, vcard_ge_main m1"
                  + " WHERE d.VGEID=m2.VGEID AND d.vCardOwner=m2.MemberID AND d.VGEID=m1.pid"
                  + "   AND d.MemberID=:MemberID AND d.DownloadTime IS NULL"
                  + " ORDER BY d.VGEID desc, m2.JoinTime, m2.MemberID";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                selCmd1.Parameters.Add("MemberID", OracleType.VarChar).Value = SIP_Num;
                OracleDataAdapter adapter1 = new OracleDataAdapter(selCmd1);
                DataSet dataSet1 = new DataSet();
                adapter1.Fill(dataSet1);

                List<Dictionary<string, string>> dictList = new List<Dictionary<string, string>>();

                foreach (DataRow row in dataSet1.Tables[0].Rows)
                {
                    Dictionary<string, string> dict = new Dictionary<string, string>();
                    dict["VGEID"] = Convert.ToString(row["VGEID"]);
                    dict["vCardOwner"] = Convert.ToString(row["vCardOwner"]);
                    dict["UpdateTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", row["UpdateTime"]);
                    dict["InitTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", row["InitTime"]);
                    dict["vCardKey"] = string.Format(@"{0}_{1:yyyyMMdd_HHmmss}", row["vCardOwner"], row["UpdateTime"]);
                    dictList.Add(dict);
                }

                return dictList.ToArray();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得 vCard 的上傳時間
        /// </summary>
        /// <param name="VGEID">群組名片交換的PID</param>
        /// <param name="SIP_Num">User的SIP帳號</param>
        /// <param name="vCardOwner">vCard上傳者的帳號</param>
        /// <returns>若有此 vCard 回傳上傳時間，否則null</returns>
        public static DateTime? getVCardTime(int VGEID, string SIP_Num, string vCardOwner)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string selStr1 = "SELECT UpdateTime"
                  + " FROM vcard_ge_dl d, vcard_ge_member m2"
                  + " WHERE d.VGEID=m2.VGEID AND d.vCardOwner=m2.MemberID"
                  + "   AND d.VGEID=:VGEID AND d.MemberID=:MemberID AND d.vCardOwner=:vCardOwner";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                selCmd1.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                selCmd1.Parameters.Add("MemberID", OracleType.VarChar).Value = SIP_Num;
                selCmd1.Parameters.Add("vCardOwner", OracleType.VarChar).Value = vCardOwner;
                object result = selCmd1.ExecuteScalar();
                if (result == null || result == DBNull.Value)
                    return null;
                else
                    return Convert.ToDateTime(result);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 記錄 vCard 確認下載時間
        /// </summary>
        /// <param name="vCardOwner">vCard上傳者的帳號</param>
        /// <param name="vCardTime">vCard上傳時間</param>
        /// <param name="SIP_Num">下載者SIP帳號</param>
        /// <returns>記錄Update筆數</returns>
        public static int UpdateDLTime(string vCardOwner, DateTime vCardTime, string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                //【01】檢查是否存在此 vCard
                //
                string selStr1 = "SELECT d.VGEID"
                  + " FROM vcard_ge_dl d, vcard_ge_member m2"
                  + " WHERE d.VGEID=m2.VGEID AND d.vCardOwner=m2.MemberID"
                  + "   AND d.MemberID=:MemberID AND d.DownloadTime IS NULL"
                  + "   AND d.vCardOwner=:vCardOwner AND m2.UpdateTime=:UpdateTime";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                selCmd1.Parameters.Add("MemberID", OracleType.VarChar).Value = SIP_Num;
                selCmd1.Parameters.Add("vCardOwner", OracleType.VarChar).Value = vCardOwner;
                selCmd1.Parameters.Add("UpdateTime", OracleType.DateTime).Value = vCardTime;
                OracleDataAdapter adapter1 = new OracleDataAdapter(selCmd1);
                DataSet dataSet1 = new DataSet();
                adapter1.Fill(dataSet1);

                if (dataSet1.Tables[0].Rows.Count == 0)
                    return 0;

                int VGEID = Convert.ToInt32(dataSet1.Tables[0].Rows[0]["VGEID"]);

                //【02】記錄確認下載時間
                //
                string updStr2 = "UPDATE vcard_ge_dl SET DownloadTime=:DateTime"
                  + " WHERE VGEID=:VGEID AND MemberID=:MemberID AND vCardOwner=:vCardOwner";
                OracleCommand updCmd2 = new OracleCommand(updStr2, connOracle);
                updCmd2.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                updCmd2.Parameters.Add("VGEID", OracleType.Int32).Value = VGEID;
                updCmd2.Parameters.Add("MemberID", OracleType.VarChar).Value = SIP_Num;
                updCmd2.Parameters.Add("vCardOwner", OracleType.VarChar).Value = vCardOwner;
                return updCmd2.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }


    }


    /// <summary>
    /// 定位資訊物件
    /// </summary>
    public class LocationInfo
    {
        DateTime _UpdateTime;//更新時間
        decimal _Latitude; //緯度
        decimal _Longitude;//經度
        int _HAccuracy;    //誤差半徑(meter)

        public DateTime UpdateTime { get { return this._UpdateTime; } }
        public decimal Latitude { get { return this._Latitude; } }
        public decimal Longitude { get { return this._Longitude; } }
        public int HAccuracy { get { return this._HAccuracy; } }


        public LocationInfo(DateTime updateTime, decimal latitude, decimal longitude, int hAccuracy)
        {
            _UpdateTime = updateTime;
            _Latitude = latitude;
            _Longitude = longitude;
            _HAccuracy = hAccuracy;
        }


        public LocationInfo(DataRow row)
        {
            _UpdateTime = Convert.ToDateTime(row["UpdateTime"]);
            _Latitude = Convert.ToDecimal(row["Latitude"]);
            _Longitude = Convert.ToDecimal(row["Longitude"]);
            _HAccuracy = Convert.ToInt32(row["HAccuracy"]);
        }
    }
}